﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

/// <summary>
/// Summary description for AdminSearch
/// </summary>
public class AdminSearch
{



    public AdminSearch()
    {
    }


    public List<SearchRow> getData(Dictionary<String, object> criteria)
    {
        var conn = DBManager.getSqlConnection();
        conn.Open();

        String input = "";
        String searType = "";

        if (criteria != null)
        {
            Object inputObj = criteria["input"];
            input = inputObj == null ? "" : (String)inputObj;

            Object searchObj = criteria["searchType"];
            searType = searchObj == null ? "" : (String)searchObj;
        }

        string sql = "";
        if (Util.IsBlank(searType))
        {
            sql = "select distinct C.userId, C.firstName+' '+C.lastName as fullName, C.nric, P.policy_no from Customer C, R_Policy P where C.userId = P.user_Id";
        }
        else
        {
            sql = "select distinct C.userId, C.firstName+' '+C.lastName as fullName, C.nric, P.policy_no, R.request_time from Customer C, R_Policy P,R_REQUEST R where R.policy_no=P.policy_no and C.userId = P.user_Id";
        }



        if (!Util.IsBlank(input))
        {
            Match match = Regex.Match(input, @"FP[0-9]+");
            // Here we check the Match instance.
            if (match.Success)
            {
                sql = sql + " AND P.policy_no like '" + input + "%'";
            }
            else
            {
                if (Regex.Match(input, @"[A-Z][0-9]+[A-Z]").Success)
                {
                    sql = sql + " AND C.NRIC like '" + input + "%'";
                }
                else
                {
                    sql = sql + " AND (C.firstName like '%" + input + "%' or  C.lastName like '%" + input + "%')";
                }

            }
        }

        if (!Util.IsBlank(searType))
        {
            if ("PREMIUM_TOP_UP".Equals(searType))
            {
                sql += " AND R.request_type='PREMIUM_TOP_UP'";
            }
            else if ("PREMIUM_TOP_UP".Equals(searType))
            {
                sql += " AND R.request_type='PREMIUM_TOP_UP'";
            }
            else if ("SWITCH_REDIRECT".Equals(searType))
            {
                sql += " AND R.request_type='SWITCH_REDIRECT'";
            }
            else if ("WITHDRAWAL_TERMINATE".Equals(searType))
            {
                sql += " AND (R.request_type='WITHDRAWAL' or  R.request_type='TERMINATE')";
            }
            else if ("CHANGE_DETAIL".Equals(searType))
            {
                sql += " AND R.request_type='CHANGE_DETAIL'";
            }

            sql += " AND R.status='PENDING'";
        }

        if (Util.IsBlank(searType))
        {
            sql += " order by  C.userId";
        }
        else
        {
            //sql += " order by  R.request_time asc";
            sql += " order by  R.request_time desc";
        }


        var cmd = new SqlCommand(sql, conn);

        List<SearchRow> listTmp = new List<SearchRow>();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            SearchRow row = new SearchRow();
            row.clientId = reader.GetInt32(0);
            row.clientName = reader.GetString(1);
            row.clientNric = reader.GetString(2);
            row.clientPolicyNo = reader.GetString(3);
            listTmp.Add(row);

        }

        reader.Close();


        //remove the same user, same policy
        Dictionary<string, SearchRow> filter = new Dictionary<string, SearchRow>();

        foreach (SearchRow searchRow in listTmp)
        {
            string key = searchRow.clientId + searchRow.clientPolicyNo;
            if (!filter.ContainsKey(key))
            {
                filter.Add(key, searchRow);
            }

        }
        List<SearchRow> list = new List<SearchRow>();

        foreach (SearchRow v in filter.Values)
        {
            list.Add(v);
        }

        foreach (SearchRow searchRow in list)
        {
            string sql2 = "select request_id, request_type, request_time FROM R_REQUEST R where R.status='PENDING' and R.policy_no='" + searchRow.clientPolicyNo + "'";
            if (!Util.IsBlank(searType))
            {
                if ("PREMIUM_TOP_UP".Equals(searType))
                {
                    sql2 += " AND R.request_type='PREMIUM_TOP_UP'";
                }
                else if ("PREMIUM_TOP_UP".Equals(searType))
                {
                    sql2 += " AND R.request_type='PREMIUM_TOP_UP'";
                }
                else if ("SWITCH_REDIRECT".Equals(searType))
                {
                    sql2 += " AND R.request_type='SWITCH_REDIRECT'";
                }
                else if ("WITHDRAWAL_TERMINATE".Equals(searType))
                {
                    sql2 += " AND (R.request_type='WITHDRAWAL' or  R.request_type='TERMINATE')";
                }
                else if ("CHANGE_DETAIL".Equals(searType))
                {
                    sql2 += " AND R.request_type='CHANGE_DETAIL'";
                }
            }
            sql2 += " order by R.request_time desc ";

            var cmd2 = new SqlCommand(sql2, conn);
            SqlDataReader reader2 = cmd2.ExecuteReader();

            while (reader2.Read())
            {
                string requestType = "";
                SearchSubRequestRow requestRow = new SearchSubRequestRow();

                if ("SWITCH_REDIRECT".Equals(reader2.GetString(1)))
                {
                    requestType = "Fund Switch / Premium Rediection ";
                    requestRow.href = "reviewFundSwitch.aspx?requestId=" + reader2.GetInt32(0) + "&policyNo=" + searchRow.clientPolicyNo;
                }
                else if ("PREMIUM_TOP_UP".Equals(reader2.GetString(1)))
                {
                    requestType = "Premium Top Up";
                    requestRow.href = "reviewPremiumTopUp.aspx?requestId=" + reader2.GetInt32(0) + "&policyNo=" + searchRow.clientPolicyNo;
                }
                else if ("WITHDRAWAL".Equals(reader2.GetString(1)))
                {
                    requestType = "Withdrawal / Terminate";
                    requestRow.href = "reviewWithdrawalTerminate.aspx?requestId=" + reader2.GetInt32(0) + "&policyNo=" + searchRow.clientPolicyNo;
                }
                else if ("TERMINATE".Equals(reader2.GetString(1)))
                {
                    requestType = "Withdrawal / Terminate";
                    requestRow.href = "reviewWithdrawalTerminate.aspx?requestId=" + reader2.GetInt32(0) + "&policyNo=" + searchRow.clientPolicyNo;
                }
                else if ("CHANGE_DETAIL".Equals(reader2.GetString(1)))
                {
                    requestType = "Change Details";
                    requestRow.href = "reviewChangeDetails.aspx?requestId=" + reader2.GetInt32(0) + "&policyNo=" + searchRow.clientPolicyNo;
                }


                requestRow.text = requestType;
                requestRow.date = reader2.GetDateTime(2);
                searchRow.clientRequests.Add(requestRow);

            }


            reader2.Close();


            SearchSubRequestRow linkRow0 = new SearchSubRequestRow();
            linkRow0.href = searchRow.clientPolicyNo + "#target_personalDetails";//Convert.ToString(reader2.GetInt32(0));
            linkRow0.text = "Personal Details";
            searchRow.clientDetailLinks.Add(linkRow0);

            SearchSubRequestRow linkRow1 = new SearchSubRequestRow();
            linkRow1.href = searchRow.clientPolicyNo + "#target_occupationalDetails";//Convert.ToString(reader2.GetInt32(0));
            linkRow1.text = "Occupational Details";
            searchRow.clientDetailLinks.Add(linkRow1);

            SearchSubRequestRow linkRow2 = new SearchSubRequestRow();
            linkRow2.href = searchRow.clientPolicyNo + "#target_planDetails";//Convert.ToString(reader2.GetInt32(0));
            linkRow2.text = "Plan Details";
            searchRow.clientDetailLinks.Add(linkRow2);

            SearchSubRequestRow linkRow3 = new SearchSubRequestRow();
            linkRow3.href = searchRow.clientPolicyNo + "#target_paymentDetails";//Convert.ToString(reader2.GetInt32(0));
            linkRow3.text = "Payment Details";
            searchRow.clientDetailLinks.Add(linkRow3);

        }

        conn.Close();

        return list;
    }

}